/**+++++++++++++++++++++++++++
 * 说明：excel模板导出公共类
 *
 * 编写：[yohuy.tung@gmail.com]
 * 创建：[2018-01-23 20:56]
 * 修改：[2021-4-26 14:38:35]
 *+++++++++++++++++++++++++++*/
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Runtime.InteropServices.ComTypes;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace ExcelHelper.NPOI
{
    public enum ExcelTypeEnum : byte
    {
        XLS,
        XLSX
    }
    public static class ExcelExtended
    {
        public static void SetCellFormartValue(this ICell cell, string v, bool resetCellType = true)
        {
            bool end = false;
            if (resetCellType)
            {
                double fv = 0d;
                DateTime date;
                if (double.TryParse(v, out fv))
                {
                    cell.SetCellValue(fv);
                    cell.SetCellType(CellType.Numeric);
                    end = true;
                }
                else if (DateTime.TryParse(v, out date))
                {
                    cell.SetCellValue(date);
                    end = true;
                }
            }

            if (!end)
            {
                v = v.Replace("<br/>", "\n");
                cell.SetCellValue(v);
            }
        }
    }

    /// <summary>
    /// Excel模板导出，内含使用说明
    /// </summary>
    public class ExcelHelper
    {
        public ExcelHelper()
        {
            WL.Core.Licensing.Amer.Singleton.Do();
        }
        #region 使用说明 [yohuy@qq.com 2020-05-26 14:41]

        // Excel模板导出使用说明：
        // x表示列，从0开始
        // y表示行，从0开始

        // 1.行循环：
        // 标签：{$LOOPY(RowData>Property, RowCount)}、{$LOOPY(RowData>Property|true|[是/否], RowCount)}
        //       Property|true: |后面表示是否自动格式化
        //       Property||[是/否]: |后面表示是否枚举项的字符串，/分隔
        //       RowData>@Index|false：行的序号
        // 行循环使用新标记：‘>’，以此来分隔集合、对象的属性取舍
        // 示例：
        // 循环行数据的直接属性  {$LOOPY(RowData>Property, RowCount)}
        // 循环行数据的属性为对象，再取对象的属性 {$LOOPY(RowData > Object.Property, RowCount)}

        // 2.列循环
        // 标签：{$LOOPX(ColumnNames)}
        // 该标记在同一行中不能使用行循环标签

        // 3.行内循环（行合并、列合并）
        // 标签：列合并{$LOOPZ(X,RowData.Object>Propety,ParentIndex)}、行合并{$LOOPZ(Y,RowData.Object>Propety,ParentIndex)}
        // RowData.Object>Propety：RowData表示行数据，Object表示行数据当中的一个对象属性，Propety表示为具体的取值属性路径，可以为多级
        // 目前该标签使用只支持2级循环，及一层行合并或列合并

        // 4.图片：
        // 标签：{$IMG(Company.Seal, dx1, dy1, dx2, dy2, col1, row1, col2, row2, OffSetY)};
        //      col1,row1表示图片的左上角坐标，col2,row2表示图片的右下角坐标，其中OffSetY表示偏移的Y值，可不填
        //         dx1：起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
        //         dy1：起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
        //         dx2：终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
        //         dy2：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
        //         col1：起始单元格列序号，从0开始计算；
        //         row1：起始单元格行序号，从0开始计算，如例子中col1 = 0,row1 = 0就表示起始单元格为A1；
        //         col2：终止单元格列序号，从0开始计算；
        //         row2：终止单元格行序号，从0开始计算，如例子中col2 = 2,row2 = 2就表示起始单元格为C3；

        // 5.文本：
        // 标签：{$TEXT(Object.Propety)}

        // 6.日期：
        // 标签：{$DATE(DateField, yyyyMMddHHmmss)}

        // 7.步骤标签：用于多数据源导出
        // 标签：{$STEP}

        // 8.结束标签查找：
        // 标签：{$END}

        // 调用示例：
        // ExportDataDto<TS, TR> exportData = new ExportDataDto<TS, TR>();
        // string template = _basePath + "ExportTemplate.xls";
        // ExcelHelper.NPOI.ExcelHelper excelHelper = new ExcelHelper.NPOI.ExcelHelper();
        // string error;
        // excelHelper.ExportToWebByTemplate(template, "Excel导出文件名.xls", exportData, out error);
        #endregion

        Regex _tagRegex = new Regex("(?<={\\$)[^}]*(?=})");
        private ExcelTypeEnum _excelType;
        private bool _continue;

        private string SerializeObject(object obj)
        {
            IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
            timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
            string json = Newtonsoft.Json.JsonConvert.SerializeObject(obj, timeFormat);
            return json;
        }
        private bool ExportByTemplate(string template, string saveFileName, object data, List<int> sheetIndex, Action<IWorkbook> callBack, out string errMsg)
        {
            var success = false;
            errMsg = null;
            if (data != null)
            {
                FileInfo fi = new FileInfo(template);
                if (fi.Exists)
                {
                    _excelType =
                        (ExcelTypeEnum)
                        Enum.Parse(typeof(ExcelTypeEnum), fi.Extension.Replace(".", string.Empty).ToUpper());
                    System.IO.FileStream fileStream = new System.IO.FileStream(template, System.IO.FileMode.Open,
                        System.IO.FileAccess.Read);

                    IWorkbook workbook;
                    if (_excelType == ExcelTypeEnum.XLS)
                    {
                        workbook = new HSSFWorkbook(fileStream);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(fileStream);
                    }

            
                    if (success)
                    {
                        //foreach (var si in sheetIndex)
                        //{
                        //    ISheet sheet = workbook.GetSheetAt(si);
                        //    var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
                        //    if (shapeContainer != null)
                        //    {
                        //        var shapeList = shapeContainer.Children;
                        //        foreach (var shape in shapeList)
                        //        {
                        //            if (shape is IPicture)
                        //            {
                        //                var picture = (IPicture)shape;
                        //                picture.Resize();
                        //            }
                        //        }
                        //    }
                        //}
                        callBack?.Invoke(workbook);
                    }
                    workbook.Close();
                    fileStream.Close();
                    fileStream.Dispose();
                }
                else
                {
                    errMsg = "模板不存在";
                }
            }
            else
            {
                errMsg = "数据为空";
            }
            return success;
        }

        /// <summary>
        /// 基于模板导出PDF
        /// </summary>
        /// <param name="template">模板文件</param>
        /// <param name="saveFileName">输出文件目录</param>
        /// <param name="data">数据</param>
        /// <param name="errMsg">错误信息</param>
        /// <returns></returns>
        public bool ExportPdfByTemplate(string template, string saveFileName, object data, out string errMsg)
        {
            Action<IWorkbook> callBack = workbook =>
            {
                using (Stream sw = new FileStream(saveFileName, FileMode.OpenOrCreate))
                {
                    workbook.Write(sw);
                }
            };

            return ExportByTemplate(template, saveFileName, data, new List<int> { 0 }, callBack, out errMsg);
        }

        /// <summary>
        /// 基于模板导出EXCEL
        /// </summary>
        /// <param name="template">模板文件</param>
        /// <param name="saveFileName">输出文件目录</param>
        /// <param name="data">数据</param>
        /// <param name="errMsg">错误信息</param>
        /// <returns></returns>
        public bool ExportByTemplate(string template, string saveFileName, object data, out string errMsg)
        {
            Action<IWorkbook> callBack = workbook =>
            {
                using (Stream sw = new FileStream(saveFileName, FileMode.OpenOrCreate))
                {
                    workbook.Write(sw);
                }
            };

            return ExportByTemplate(template, saveFileName, data, new List<int> { 0 }, callBack, out errMsg);
        }

        /// <summary>
        /// 基于模板导出EXCEL,用于web下载
        /// </summary>
        /// <param name="template">模板文件</param>
        /// <param name="saveFileName">输出文件目录</param>
        /// <param name="data">数据</param>
        /// <param name="errMsg">错误信息</param>
        public bool ExportToWebByTemplate(string template, string saveFileName, object data, out string errMsg)
        {
            return ExportToWebByTemplate(template, saveFileName, data, new List<int> { 0 }, out errMsg);
        }

        /// <summary>
        /// 基于模板导出EXCEL,用于web下载
        /// </summary>
        /// <param name="template">模板文件</param>
        /// <param name="saveFileName">输出文件目录</param>
        /// <param name="data">数据</param>
        /// <param name="sheetIndex">多SHEET索引</param>
        /// <param name="errMsg">错误信息</param>
        public bool ExportToWebByTemplate(string template, string saveFileName, object data, List<int> sheetIndex, out string errMsg)
        {
            Action<IWorkbook> callBack = workbook =>
            {
                HttpContext curContext = HttpContext.Current;

                // 设置编码和附件格式
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = Encoding.UTF8;
                curContext.Response.Charset = "GB2312";
                curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(saveFileName, Encoding.UTF8));
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    //ms.Flush();
                    //ms.Position = 0;
                    curContext.Response.BinaryWrite(ms.GetBuffer());
                }
                curContext.Response.End();
            };

            return ExportByTemplate(template, saveFileName, data, sheetIndex, callBack, out errMsg);
        }
        static object _locker = new object();
        /// <summary>
        /// 基于模板导出EXCEL,用于web下载
        /// </summary>
        /// <param name="template">模板文件</param>
        /// <param name="saveFileName">输出文件目录</param>
        /// <param name="data">数据</param>
        /// <param name="sheetIndex">多SHEET索引</param>
        /// <param name="errMsg">错误信息</param>
        public bool ExportPdfToWebByTemplate(string foldpath, string template, string saveFileName, object data, out string errMsg)
        {
            Action<IWorkbook> callBack = workbook =>
            {
                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                        //ms.Flush();
                        //ms.Position = 0;
                        ExportPdfToWebByTemplate(foldpath, saveFileName, ms);
                }
            };

            return ExportByTemplate(template, saveFileName, data, new List<int> { 0 }, callBack, out errMsg);
        }
        public bool ExportPdfToWebByTemplate(string foldpath, string saveFileName, MemoryStream mStream)
        {
            var temp = foldpath + "/_tmp";
            var tmpFile = $"{temp}/{Guid.NewGuid():N}";

            lock (_locker)
            {
                if (!Directory.Exists(temp))
                {
                    Directory.CreateDirectory(temp);
                }

                try
                {
                    var files = Directory.GetFiles(temp);
                    if (files.Length > 0)
                    {
                        foreach (var item in files)
                        {
                            File.Delete(item);
                        }
                    }
                }
                catch
                {

                }

                using (FileStream fs = new FileStream(tmpFile + ".xls", FileMode.Create))
                {
                    BinaryWriter w = new BinaryWriter(fs);
                    w.Write(mStream.ToArray());
                    mStream.Close();
                }

                if (CovertExcelToPDF(tmpFile + ".xls", tmpFile + ".pdf"))
                {
                    HttpContext curContext = HttpContext.Current;
                    // 设置编码和附件格式
                    curContext.Response.ContentType = "application/pdf";
                    curContext.Response.ContentEncoding = Encoding.UTF8;
                    curContext.Response.Charset = "GB2312";
                    curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(saveFileName, Encoding.UTF8));

                    using (FileStream fs = File.Open(tmpFile + ".pdf", FileMode.Open))
                    {
                        byte[] infbytes = new byte[(int)fs.Length];
                        fs.Read(infbytes, 0, infbytes.Length);
                        curContext.Response.BinaryWrite(infbytes);
                    }
                    curContext.Response.End();
                }


            }
            return true;
        }
        /// <summary>
        /// 获取模板标签列表
        /// </summary>
        /// <param name="sheet">当前操作的sheet</param>
        /// <param name="tagDict">标签集合</param>
        /// <param name="errMsg">操作失败时返回错误原因，成功返回空串</param>
        /// <returns>操作成功返回True，否则返回false</returns>
        private bool GetTags(ISheet sheet, out Dictionary<int[], string[]> tagDict, out string errMsg, bool endStep = false)
        {
            errMsg = null;
            tagDict = new Dictionary<int[], string[]>();
            bool result;
            try
            {
                bool end = false;
                Regex regex = new Regex("\\s");
                for (int n = sheet.FirstRowNum; n <= sheet.LastRowNum; n++)
                {
                    IRow row = sheet.GetRow(n);
                    if (row != null)
                    {
                        for (int i = 0; i < row.Cells.Count; i++)
                        {
                            ICell cell = row.Cells[i];

                            string text = cell.ToString().Trim();
                            if (text.Contains("{$"))
                            {
                                int[] key = new int[]
                                {
                                    cell.ColumnIndex,
                                    row.RowNum
                                };
                                MatchCollection matchCollection = _tagRegex.Matches(text);
                                string[] array2 = null;
                                if (matchCollection.Count > 0)
                                {
                                    array2 = new string[matchCollection.Count];
                                    for (int j = 0; j < matchCollection.Count; j++)
                                    {
                                        array2[j] = regex.Replace(matchCollection[j].Value, string.Empty);
                                    }
                                }
                                if (text == "{$STEP}")
                                {
                                    _continue = true;
                                    if (endStep)
                                    {
                                        cell.SetCellValue(string.Empty);
                                    }
                                    end = true;
                                    break;
                                }
                                else if (text == "{$END}")
                                {
                                    // [yohuy 2018-01-12 10:13]
                                    // 清除结束标记
                                    _continue = false;
                                    cell.SetCellValue(string.Empty);
                                    end = true;
                                    break;
                                }
                                else
                                {
                                    tagDict.Add(key, array2);
                                }
                            }
                        }
                        if (end)
                        {
                            break;
                        }
                    }
                }

                result = true;
            }
            catch (System.Exception ex)
            {
                errMsg = ex.Message;
                result = false;
            }
            return result;
        }

        private ICell CopyCell(IRow row, int sourceCellIndex, int targetCellIndex)
        {
            ICell cell = null;
            var sourceCell = row.GetCell(sourceCellIndex);
            var targetCell = row.GetCell(targetCellIndex);
            if (targetCell != null)
            {
                cell = row.CreateCell(targetCellIndex);
                cell.CellStyle = sourceCell.CellStyle;
                cell.SetCellType(sourceCell.CellType);
                switch (sourceCell.CellType)
                {
                    case CellType.Numeric:
                        cell.SetCellValue(sourceCell.NumericCellValue);
                        break;
                    case CellType.String:
                        cell.SetCellValue(sourceCell.StringCellValue);
                        break;
                    default:
                        cell.SetCellValue(sourceCell.ToString());
                        break;
                }
            }
            else
            {
                cell = row.CopyCell(sourceCellIndex, targetCellIndex);
            }
            return cell;
        }

        /// <summary>
        /// 替换模板标签内容
        /// </summary>
        /// <param name="workbook">当前操作的Workbook</param>
        /// <param name="fileType">模板文件类型：XLS；XLSX；</param>
        /// <param name="jsonText">待替换的原始数据（JSON格式串）</param>
        /// <param name="errMsg">操作失败时返回错误原因，成功返回空串</param>
        /// <returns>操作成功返回True，否则返回false</returns>
        private bool ReplaceTags(IWorkbook workbook, ISheet sheet, string jsonText, out string errMsg)
        {
            errMsg = string.Empty;
            bool result;
            try
            {
                Dictionary<int[], string[]> tagDict;
                if (!this.GetTags(sheet, out tagDict, out errMsg))
                {
                    result = false;
                }
                else
                {
                    JObject jObject = JObject.Parse(jsonText);
                    DataTable loopRowDt = new DataTable();
                    loopRowDt.Columns.Add("PointX", System.Type.GetType("System.Int32"));
                    loopRowDt.Columns.Add("PointY", System.Type.GetType("System.Int32"));
                    loopRowDt.Columns.Add("Length", System.Type.GetType("System.Int32"));

                    bool hasX = false;
                    foreach (KeyValuePair<int[], string[]> current in tagDict)
                    {
                        int x = current.Key[0];
                        int y = current.Key[1];
                        string[] value = current.Value;
                        ICell cell = sheet.GetRow(y).GetCell(x);
                        string[] tags = value;
                        for (int i = 0; i < tags.Length; i++)
                        {
                            string tag = tags[i];
                            if (tag.Contains("IMG("))
                            {
                                cell.SetCellValue(string.Empty);
                                this.ImgFormula(workbook, sheet, jObject, tag);
                            }
                            else if (tag.Contains("DATE("))
                            {
                                cell.SetCellFormartValue(cell.ToString().Replace("{$" + tag + "}", DateFormula(jObject, tag)), false);
                            }
                            else if (tag.Contains("TEXT("))
                            {
                                cell.SetCellFormartValue(cell.ToString().Replace("{$" + tag + "}", TextFormula(jObject, tag)), false);
                            }
                            else if (!tag.Contains("SUM(") && !tag.Contains("LOOPZ("))// && !tag.Contains("LOOPX("))
                            {
                                if (tag.Contains("LOOPY("))
                                {
                                    string tagText = tag.Replace("LOOPY(", string.Empty).Replace(")", string.Empty);
                                    string[] tagArray = tagText.Split(new char[]
                                    {
                                        ','
                                    });
                                    DataRow dataRow = loopRowDt.NewRow();
                                    dataRow["PointX"] = x;
                                    dataRow["PointY"] = y;
                                    dataRow["Length"] = jObject.SelectToken(tagArray[1]).Value<int>();
                                    loopRowDt.Rows.Add(dataRow);
                                }
                                // [yohuy 2018-01-12 10:48]
                                // 自增列标签
                                else if (tag.Contains("LOOPX("))
                                {
                                    hasX = true;
                                }
                                else
                                {
                                    cell.SetCellFormartValue(cell.ToString().Replace("{$" + tag + "}", this.SimpleFormula(jObject, tag)));
                                }
                            }
                        }
                    }

                    #region X循环 [yohuy 2018-05-22 11:41]

                    if (hasX)
                    {
                        foreach (KeyValuePair<int[], string[]> current in tagDict)
                        {
                            int x = current.Key[0];
                            int y = current.Key[1];
                            string[] value = current.Value;
                            string[] tags = value;
                            for (int i = 0; i < tags.Length; i++)
                            {
                                string tag = tags[i];
                                if (tag.Contains("LOOPX("))
                                {
                              
                                    if (loopCount > 1)
                                    {
                                        var moveCellCount = curRow.LastCellNum - x - 1;
                                        for (; moveCellCount > 0; moveCellCount--)
                                        {
                                            var cellIndex = x + moveCellCount;
                                            //curRow.GetCell(cellIndex).SetCellType(CellType.String);
                                            //curRow.CopyCell(cellIndex, cellIndex + loopCount - 1);
                                            CopyCell(curRow, cellIndex, cellIndex + loopCount - 1);
                                        }
                                    }
                                    int moveIndex = 0;
                                    foreach (string column in columns)
                                    {
                                        //curCell.SetCellType(CellType.String);
                                        var cellT = moveIndex > 0 ? CopyCell(curRow, x, x + moveIndex) : curCell;
                                        cellT.SetCellFormartValue(column);
                                        moveIndex++;
                                    }
                                }
                            }
                        }
                    }
                    #endregion

                    this.CreateOutputCells(sheet, loopRowDt);
                    if (!this.GetTags(sheet, out tagDict, out errMsg))
                    {
                        result = false;
                    }
                    else
                    {
                        foreach (KeyValuePair<int[], string[]> current2 in tagDict)
                        {
                            int[] key2 = current2.Key;
                            string[] value2 = current2.Value;
                            string[] array4 = value2;
                            for (int j = 0; j < array4.Length; j++)
                            {
                                string text3 = array4[j];
                                if (text3.Contains("LOOPY("))
                                {
                                    this.CirculateFormula(sheet, jObject, text3, key2);
                                }
                            }
                        }
                        if (!this.GetTags(sheet, out tagDict, out errMsg, true))
                        {
                            result = false;
                        }
                        else
                        {
                            bool loopX = false;
                            bool loopY = false;
                            int loopXOffset = 0;
                            int loopYOffset = 0;
                            int LastLoopYOffset = 1;
                            int lastLoopYRowIndex = 0;
                            foreach (KeyValuePair<int[], string[]> current3 in tagDict)
                            {
                                int[] key3 = current3.Key;
                                string[] value3 = current3.Value;
                                ICell cell2 = sheet.GetRow(key3[1]).GetCell(key3[0]);
                                string[] array5 = value3;
                                for (int k = 0; k < array5.Length; k++)
                                {
                                    string text4 = array5[k];
                                    if (text4.Contains("SUM("))
                                    {
                                        this.SumFormula(sheet, cell2, jObject, text4);
                                    }

                                    #region Z系列循环 [yohuy 2018-05-22 11:25]

                                    if (text4.Contains("LOOPZ("))
                                    {
                                        string text2 = text4.Replace("LOOPZ(", string.Empty).Replace(")", string.Empty);
                                        var curRow = sheet.GetRow(key3[1]);
                                        var curCell = curRow.GetCell(key3[0]);


                                        string[] param = text2.Split(',');
                                        var type = param[0].ToUpper();
                                        var splitArray = param[1].Split('>');
                                        string listProperty = splitArray[0];
                                        var rowNode = listProperty.Substring(0, listProperty.LastIndexOf('.'));
                                        string children = listProperty.Substring(listProperty.LastIndexOf('.') + 1);
                                        string property = splitArray[1];
                                        bool autoFormat = true;



                                        int parentIndex;
                                        int.TryParse(param[2], out parentIndex);

                                        var parent = jObject.SelectToken(rowNode).Children().ToList()[parentIndex];

                                        var loopNode = parent.SelectToken(children).Children();
                                        var loopCount = loopNode.Count();
                                        if (loopCount > 1)
                                        {
                                            if (type == "X")
                                            {
                                                var moveCellCount = curRow.LastCellNum - key3[0] - 1;
                                                for (; moveCellCount > 0; moveCellCount--)
                                                {
                                                    var cellIndex = key3[0] + moveCellCount;
                                                    //curRow.GetCell(cellIndex).SetCellType(CellType.String);
                                                    //curRow.CopyCell(cellIndex, cellIndex + loopCount - 1);
                                                    CopyCell(curRow, cellIndex, cellIndex + loopCount - 1);
                                                }
                                                if (!loopX)
                                                {
                                                    loopX = true;
                                                    loopXOffset = loopCount - 1;
                                                }
                                            }
                                            else if (type == "Y")
                                            {
                                                bool merge = true;
                                                if (!loopY)
                                                {
                                                    loopY = true;
                                                    loopYOffset = loopCount - 1;
                                                }
                                                else if (lastLoopYRowIndex != key3[1])
                                                {
                                                    LastLoopYOffset = loopYOffset + 1;
                                                    loopYOffset += loopCount - 1;
                                                }
                                                else
                                                {
                                                    merge = false;
                                                }
                                                if (merge)
                                                {
                                                    InsertRows(ref sheet, key3[1] + LastLoopYOffset, loopCount - 1);

                                                    int curRowIndex = key3[1] + LastLoopYOffset - 1;
                                                    curRow = sheet.GetRow(curRowIndex);
                                                    for (int ci = curRow.FirstCellNum; ci < curRow.LastCellNum; ci++)
                                                    {
                                                        ICell cellSource = curRow.GetCell(ci);
                                                        if (cellSource != null && !cellSource.ToString().Contains("LOOPZ(Y"))
                                                        {
                                                            CellRangeAddress cra = new CellRangeAddress(curRowIndex,
                                                                key3[1] + LastLoopYOffset + loopCount - 2, ci, ci);
                                                            sheet.AddMergedRegion(cra);
                                                        }
                                                    }
                                                    lastLoopYRowIndex = key3[1];
                                                }
                                            }
                                        }
                                        else
                                        {
                                            if (type == "Y")
                                            {
                                                var offset = lastLoopYRowIndex == key3[1]
                                                    ? LastLoopYOffset - 1
                                                    : loopYOffset;
                                                var cell = sheet.GetRow(key3[1] + offset).GetCell(key3[0] + loopXOffset);
                                                cell.SetCellFormartValue(string.Empty);
                                            }
                                        }

                                        int moveIndex = 0;
                                        foreach (JToken item in loopNode)
                                        {
                                            string v = item.SelectToken(property).ToString();
                                            ICell cell = null;
                                            if (type == "X")
                                            {
                                                //curCell.SetCellType(CellType.String);
                                                cell = moveIndex > 0
                                                   ? CopyCell(curRow, key3[0], key3[0] + moveIndex) // curCell.CopyCellTo(key3[0] + moveIndex) 
                                                   : curCell;
                                            }
                                            else if (type == "Y")
                                            {
                                                var offset = lastLoopYRowIndex == key3[1]
                                                    ? LastLoopYOffset - 1
                                                    : loopYOffset;
                                                cell = sheet.GetRow(key3[1] + offset + moveIndex).GetCell(key3[0] + loopXOffset);
                                            }
                                            cell.SetCellFormartValue(v);
                                            moveIndex++;
                                        }
                                    }
                                    #endregion
                                }
                            }
                            result = true;
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                errMsg = ex.Message;
                result = false;
            }
            return result;
        }
        private void InsertRows(ref ISheet sheet1, int fromRowIndex, int rowCount)
        {
            sheet1.ShiftRows(fromRowIndex, sheet1.LastRowNum, rowCount, true, false);

            for (int rowIndex = fromRowIndex; rowIndex < fromRowIndex + rowCount; rowIndex++)
            {
                IRow rowSource = sheet1.GetRow(rowIndex - 1);
                IRow rowInsert = sheet1.CreateRow(rowIndex);
                rowInsert.Height = rowSource.Height;
                for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
                {
                    ICell cellSource = rowSource.GetCell(colIndex);
                    ICell cellInsert = rowInsert.CreateCell(colIndex);
                    if (cellSource != null)
                    {
                        cellInsert.CellStyle = cellSource.CellStyle;
                    }
                }
            }
        }
        private void CreateOutputCells(ISheet sheet, DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return;
            }

            var query = from t in dt.AsEnumerable()
                        group t by new { t1 = t.Field<int>("PointY") } into m
                        select new
                        {
                            PointX = m.Min(n => n.Field<int>("PointX")),
                            PointY = m.Key.t1,
                            Length = m.Max(n => n.Field<int>("Length"))
                        };

            var yLine = from t in query
                        orderby t.PointY descending
                        select t;

            //添加新行,从下往上依次添加
            IRow referenceRow = null;
            foreach (var item in yLine.ToList())
            {
                // [yohuy 2018-05-25 18:09]
                // 如果循环行数为1，则不需要复制新行
                if (item.Length <= 1)
                {
                    continue;
                }
                //获取样式参考行
                referenceRow = sheet.GetRow(item.PointY);

                if (referenceRow == null)
                {
                    continue;
                }

                if (sheet.LastRowNum > referenceRow.RowNum)
                {
                    //向下移动行，预留新增行的空位
                    sheet.ShiftRows
                    (
                        referenceRow.RowNum + 1, //移动开始行
                        sheet.LastRowNum, //移动结束行
                        item.Length - 1, //移动行数
                        true, //是否复制行高
                        false //是否重置行高
                    );
                }
                //计算合并单元格
                List<CellRangeAddress> referenceCra = new List<CellRangeAddress>();
                for (int i = 0; i < sheet.NumMergedRegions; i++)
                {
                    CellRangeAddress cra = sheet.GetMergedRegion(i);
                    if (cra != null && cra.FirstRow == cra.LastRow && cra.FirstRow == item.PointY)
                    {
                        referenceCra.Add(cra);
                    }
                }

                //参照首行样式，创建新行
                for (int i = 1; i < item.Length; i++)
                {
                    IRow row = sheet.GetRow(item.PointY + i);
                    if (row == null)
                    {
                        row = sheet.CreateRow(item.PointY + i);
                    }

                    row.Height = referenceRow.Height;

                    for (int m = referenceRow.FirstCellNum; m <= referenceRow.LastCellNum; m++)
                    {
                        ICell referenceCell = referenceRow.GetCell(m);
                        if (referenceCell != null)
                        {
                            ICell cell = row.CreateCell(m);
                            cell.CellStyle = referenceCell.CellStyle;
                            cell.SetCellType(referenceCell.CellType);

                            var referenceCellValue = referenceCell.ToString();
                            string cellValue;
                            if (referenceCellValue.Contains("LOOPZ"))
                            {
                                cellValue = referenceCellValue.Replace("ParentIndex", i.ToString());
                            }
                            else
                            {
                                cellValue = string.Empty;
                            }

                            cell.SetCellFormartValue(cellValue);
                        }
                    }

                    for (int m = 0; m < referenceCra.Count; m++)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(item.PointY + i,
                            item.PointY + i, referenceCra[m].FirstColumn, referenceCra[m].LastColumn));
                    }
                }
            }
        }
        /// <summary>
        /// 简单的取值公式
        /// </summary>
        /// <param name="jsonObject">原始JSON Object</param>
        /// <param name="tag">待取值的标签{$DataField}</param>
        /// <returns></returns>
        private string SimpleFormula(JObject jsonObject, string tag)
        {
            string result;
            try
            {
                string text = jsonObject.SelectToken(tag).ToString();
                text = text.Replace("<br/>", "\n");
                text = text.Replace("&nbsp;", string.Empty);
                text = this.StripHtml(text);
                result = text;
            }
            catch (System.Exception e)
            {
                result = string.Empty;
            }
            return result;
        }

        /// <summary>
        /// 将Html标签转化为空格
        /// </summary>
        /// <param name="strHtml">待转化的字符串</param>
        /// <returns>经过转化的字符串</returns>
        private string StripHtml(string strHtml)
        {
            Regex regex = new Regex("<(.|\n)+?>");
            return regex.Replace(strHtml, string.Empty);
        }

        /// <summary>
        /// 日期格式化公式
        /// </summary>
        /// <param name="jsonObject">原始JSON Object</param>
        /// <param name="tag">待格式化的标签{$DATE(DateField, yyyyMMddHHmmss)}</param>
        /// <returns></returns>
        private string DateFormula(JObject jsonObject, string tag)
        {
            string result = null;
            try
            {
                tag = tag.Replace("DATE(", string.Empty).Replace(")", string.Empty);
                string[] array = tag.Split(new char[]
                {
                    ','
                });
                string value = jsonObject.SelectToken(array[0]).ToString();
                if (!string.IsNullOrEmpty(value))
                {
                    result = System.Convert.ToDateTime(value).ToString(array[1]);
                }
            }
            catch (System.Exception e)
            {
                result = string.Empty;
            }
            return result;
        }

        private string TextFormula(JObject jsonObject, string tag)
        {
            string result = null;
            try
            {
                tag = tag.Replace("TEXT(", string.Empty).Replace(")", string.Empty);
                result = jsonObject.SelectToken(tag).ToString();
            }
            catch (System.Exception e)
            {
                result = string.Empty;
            }
            return result;
        }
        /// <summary>
        /// 插入图片公式
        /// </summary>
        /// <param name="workbook">当前操作的workbook</param>
        /// <param name="sheet">当前操作的Sheet</param>
        /// <param name="jsonObject">原始JSON Object</param>
        /// <param name="tag">待格式化的标签{$DATE(DateField, yyyyMMddHHmmss)}</param>
        /// <returns></returns>
        private bool ImgFormula(IWorkbook workbook, ISheet sheet, JObject jsonObject, string tag)
        {
            bool result = false;
            try
            {
                tag = tag.Replace("IMG(", string.Empty).Replace(")", string.Empty);
                string[] array = tag.Split(new char[]
                {
                    ','
                });
                int offsetY = 0;
                if (array.Length == 10)
                {
                    offsetY = jsonObject.SelectToken(array[9]).Value<int>();
                }
                string img = jsonObject.SelectToken(array[0]).ToString();
                if (File.Exists(img))
                {
                    byte[] pictureData = System.IO.File.ReadAllBytes(img);
                    CreatePicture(workbook, sheet, array, offsetY, pictureData);
                    result = true;
                }
                else if (img.Contains("http"))
                {
                    // [yohuy@qq.com 2020-05-22 16:41]
                    // 网络图片
                    Uri uri = new Uri(img); //imgPath :网络图片地址
                    WebRequest webRequest = WebRequest.Create(uri);
                    using (WebResponse webResponse = webRequest.GetResponse())
                    {
                        Bitmap bitmap = new Bitmap(webResponse.GetResponseStream());

                        using (MemoryStream ms = new MemoryStream())
                        {
                            bitmap.Save(ms, System.Drawing.Imaging.ImageFormat.Png);

                            byte[] pictureData = ms.ToArray();

                            CreatePicture(workbook, sheet, array, offsetY, pictureData);

                            result = true;
                        }
                        bitmap.Dispose();
                    }
                }
            }
            catch (System.Exception e)
            {
                result = false;
            }
            return result;
        }

        private void CreatePicture(IWorkbook workbook, ISheet sheet, string[] array, int offsetY, byte[] pictureData)
        {
            int pictureIdx = workbook.AddPicture(pictureData, PictureType.PNG);
            IDrawing drawing = sheet.CreateDrawingPatriarch();
            IClientAnchor anchor = workbook.GetCreationHelper().CreateClientAnchor();
            //图片位置，图片左上角为（col, row）
            anchor.Col1 = int.Parse(array[5]);
            anchor.Row1 = int.Parse(array[6]) + offsetY;
            IPicture pict = drawing.CreatePicture(anchor, pictureIdx);
            pict.Resize(); //用图片原始大小来显示
            //if (_excelType == ExcelTypeEnum.XLS)
            //{
            //    HSSFPatriarch hSSFPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
            //    HSSFClientAnchor anchor = new HSSFClientAnchor(int.Parse(array[1]), int.Parse(array[2]),
            //        int.Parse(array[3]), int.Parse(array[4]), int.Parse(array[5]), int.Parse(array[6]) + offsetY,
            //        int.Parse(array[7]), int.Parse(array[8]) + offsetY);
            //    anchor.AnchorType = AnchorType.MoveDontResize;
            //    var pic = (HSSFPicture)hSSFPatriarch.CreatePicture(anchor, pictureIdx);
            //    pic.Resize();
            //}
            //else
            //{
            //    XSSFDrawing xssfDrawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
            //    XSSFClientAnchor anchor = new XSSFClientAnchor(int.Parse(array[1]), int.Parse(array[2]),
            //        int.Parse(array[3]), int.Parse(array[4]), int.Parse(array[5]), int.Parse(array[6]) + offsetY,
            //        int.Parse(array[7]), int.Parse(array[8]) + offsetY);
            //    anchor.AnchorType = AnchorType.MoveDontResize;
            //    var pic = (XSSFPicture)xssfDrawing.CreatePicture(anchor, pictureIdx);
            //    pic.Resize();
            //}
        }

        /// <summary>
        /// 求合公式
        /// </summary>
        /// <param name="sheet">当前操作的Sheet</param>
        /// <param name="currentCell">结果存入的单元格</param>
        /// <param name="jsonObject">原始JSON Object</param>
        /// <param name="tag">待取值的标签{$SUM(StartPoint, Direction, CountField)}</param>
        /// <returns></returns>
        private bool SumFormula(ISheet sheet, ICell currentCell, JObject jsonObject, string tag)
        {
            bool result;
            try
            {
                currentCell.SetCellValue(string.Empty);
                tag = tag.Replace("SUM(", string.Empty).Replace(")", string.Empty);
                string[] array = tag.Split(new char[]
                {
                    ','
                });
                string input = array[0];
                int num = System.Convert.ToInt32(array[1]);
                int num2 = System.Convert.ToInt32(jsonObject.SelectToken(array[2]));
                if (num == 0)
                {
                    int num3 = (int)(char.Parse(Regex.Replace(input, "\\d", string.Empty)) - char.Parse("A"));
                    int rownum = int.Parse(Regex.Replace(input, "[^\\d]", string.Empty)) - 1;
                    double num4 = 0.0;
                    for (int i = num3; i < num3 + num2; i++)
                    {
                        string stringCellValue = sheet.GetRow(rownum).GetCell(i).ToString();
                        if (stringCellValue != string.Empty)
                        {
                            num4 += System.Convert.ToDouble(stringCellValue);
                        }
                    }
                    currentCell.SetCellValue(num4);
                }
                else if (num == 1)
                {
                    Regex.Replace(input, "\\d", string.Empty);
                    int cellnum = (int)(char.Parse(Regex.Replace(input, "\\d", string.Empty)) - char.Parse("A"));
                    int num5 = int.Parse(Regex.Replace(input, "[^\\d]", string.Empty)) - 1;
                    double num6 = 0.0;
                    for (int j = num5; j < num5 + num2; j++)
                    {
                        string stringCellValue2 = sheet.GetRow(j).GetCell(cellnum).ToString();
                        if (stringCellValue2 != string.Empty)
                        {
                            num6 += System.Convert.ToDouble(stringCellValue2);
                        }
                    }
                    currentCell.SetCellValue(num6);
                }
                result = true;
            }
            catch (System.Exception e)
            {
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 循环输出公式
        /// </summary>
        /// <param name="sheet">当前操作的Sheet</param>
        /// <param name="jsonObject">原始JSON Object</param>
        /// <param name="tag">待取值的标签{$LOOPY(DataField,Direction, CountField)}</param>
        /// <param name="position">循环体中的第一个单元格坐标</param>
        /// <returns></returns>
        private bool CirculateFormula(ISheet sheet, JObject jsonObject, string tag, int[] position)
        {
            bool result;
            try
            {
                tag = tag.Replace("LOOPY(", string.Empty).Replace(")", string.Empty);
                string[] array = tag.Split(',');
                var obj = array[0].Split('>');

                // [yohuy 2018-06-20 17:09]
                // 行循环使用新标记：‘>’，以此来分隔集合、对象的属性取舍

                // 示例：
                // 循环行数据的直接属性  {$LOOPY(Data > OrderNo, TotalCount)}
                // 循环行数据的属性为对象，再取对象的属性 {$LOOPY(Data > Client.ShortName, TotalCount)}
                // {$LOOPY(Data > OrderNo|true, TotalCount)}: |后面表示是否自动格式化
                string listProperty = obj[0];
                string property = obj[1];
                bool autoFormat = true;
                string[] enumValue = null;
                if (property.Contains("|"))
                {
                    var propertyConfig = property.Split('|');
                    property = propertyConfig[0];
                    bool.TryParse(propertyConfig[1], out autoFormat);
                    if (propertyConfig.Length == 3 && !string.IsNullOrEmpty(propertyConfig[2]))
                    {
                        var enumStr = propertyConfig[2].Replace("[", string.Empty).Replace("]", string.Empty);
                        enumValue = enumStr.Split('/');
                    }
                }
                int rowIndex = 0;
                string text;
                JToken current;
                var jtokens = jsonObject.SelectToken(listProperty).Children().ToList();
                for (int i = 0; i < jtokens.Count(); i++)
                {
                    text = null;
                    if (property == "@Index")
                    {
                        text = (i + 1).ToString();
                    }
                    else
                    {
                        current = jtokens[i];
                        text = current.SelectToken(property)?.Value<string>() ?? string.Empty;
                        text = text.Replace("<br/>", "\n");
                        int enumIndex;
                        if (enumValue != null && enumValue.Length > 0 && int.TryParse(text, out enumIndex))
                        {
                            text = enumValue[enumIndex];
                        }
                    }
                    sheet.GetRow(position[1] + rowIndex).GetCell(position[0]).SetCellFormartValue(text, autoFormat);
                    rowIndex++;
                }
                result = true;
            }
            catch (System.Exception e)
            {
                result = false;
            }
            return result;
        }



        /// <summary>
        /// Excel保存PDF
        /// </summary>
        /// <param name="excelPath"> EXCEL全路径 </param>
        /// <param name="pdfPath"> PDF保存路径 </param>
        /// <returns></returns>
        private bool CovertExcelToPDF(string excelPath, string pdfPath)
        {
            object missing = Type.Missing;
            //创建excel应用程序实例
            Microsoft.Office.Interop.Excel.ApplicationClass application = null;
            //创建工作薄实例
            Microsoft.Office.Interop.Excel.Workbook workBook = null;
            try
            {
                application = new Microsoft.Office.Interop.Excel.ApplicationClass();
                //打开工作簿
                workBook = application.Workbooks.Open(excelPath, missing, missing, missing, missing, missing,
                                                      missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //打开sheet
                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.Item[1];
                //设置打印放放为水平
                //ws.PageSetup.Orientation = XlPageOrientation.xlPortrait; // 垂直
                //ws.PageSetup.Orientation = XlPageOrientation.xlLandscape;  // 水平
                //设置打印时excel内容在一个页面上显示。Zoom必须设置为false
                //ws.PageSetup.Zoom = false;
                //ws.PageSetup.FitToPagesTall = 1;
                //ws.PageSetup.FitToPagesWide = 1;

                //将工作簿发布为PDF或XPS格式
                ws.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, pdfPath
                    , Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard
                    , true
                    , false     //忽略打印区域
                    , missing, missing, missing, missing);
                return true;
            }
            catch
            {
                throw;
            }
            finally
            {
                //工作簿关闭
                if (workBook != null)
                {
                    workBook.Close(true, missing, missing);
                    workBook = null;
                }
                //excel应用程序退出关闭
                if (application != null)
                {
                    application.Quit();
                    application = null;
                }
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        // [yohuy@qq.com 2021-04-30 9:32]
        // 自动列宽、行高
        public static void AutoSizeColumn(ISheet sheet, int y1, int y2, int x1, int x2)
        {
            int maxWidth = 50;
            //for (int i = 0; i <= colLastIndex; i++)
            //{
            //    sheet.AutoSizeColumn(i);
            //}
            Dictionary<int, int> rowHeight = new Dictionary<int, int>();
            //获取当前列的宽度，然后对比本列的长度，取最大值
            for (int columnNum = x1; columnNum <= x2; columnNum++)
            {
                double columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                double oldWidth = columnWidth;
                for (int rowNum = y1; rowNum <= y2; rowNum++)
                {
                    IRow currentRow = sheet.GetRow(rowNum);
                    if (currentRow != null)
                    {
                        var currentCell = currentRow.GetCell(columnNum);
                        if (currentCell != null)
                        {
                            double length = Encoding.Default.GetBytes(currentCell.ToString()).Length * 1.5;
                            if (columnWidth < length)
                            {
                                if (length > maxWidth)
                                {
                                    if (!rowHeight.ContainsKey(rowNum))
                                    {
                                        rowHeight.Add(rowNum, 1);
                                    }

                                    var hLine = (int)(length / maxWidth) + 1;
                                    length = maxWidth;
                                    if (rowHeight[rowNum] < hLine)
                                    {
                                        rowHeight[rowNum] = hLine;
                                        currentRow.Height = (short)(20 * 20 * hLine);
                                    }
                                }
                                columnWidth = length;
                            }
                        }
                    }
                }

                if (oldWidth != columnWidth)
                {
                    sheet.SetColumnWidth(columnNum, (int)(columnWidth * 256));
                }
            }
        }
    }
}
